"""
Case Type   : fullsql
Case Name   : track_stmt_stat_level参数gs_guc reload异常输入校验,取值范围:L0,L1,L2,OFF
Create At   : 2022/02/07
Owner       : zhao-pengcheng-g
Description :
    1.设置为OFF,OFF,查看参数
    2.设置为小写off,查看参数
    3.log_min_duration_statement设置为0，执行sql语句，查询statement_history表记录
    4.设置为小写l，查看参数,
    5.设置为默认值，查看参数
    6.设置为取值范围外的值，查看参数
    7.设置为不带L的值，查看参数
    8.设置分隔符为逗号以外的符号，查看参数
    9.设置为Level格式的值，查看参数
    10.环境清理
Expect      :
    1.设置成功
    2.设置成功
    3.设置成功,执行sql语句成功，statement_history表数据无记录
    4..设置成功
    5.设置成功
    6.guc执行成功，查询参数仍为上次修改后的值
    7.guc执行成功，查询参数仍为上次修改后的值
    8.guc执行成功，查询参数仍为上次修改后的值
    9.guc执行成功，查询参数仍为上次修改后的值
    10.环境清理成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0026 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 't_fullsql_0026'

    def test_fullsql(self):
        text = '---step1:设置为OFF,OFF,查看参数,expect:设置成功---'
        self.logger.info(text)
        self.config_level = 'track_stmt_stat_level'
        self.show_level_cmd = f'show {self.config_level};'
        check_level = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(check_level)
        self.level_default = check_level.splitlines()[-2].strip()
        if 'OFF,OFF' != self.level_default:
            guc_level = self.sh_primy.execute_gsguc('reload',
                                                    self.constant.GSGUC_SUCCESS_MSG,
                                                    f"{self.config_level}='OFF,OFF'")
            self.assertTrue(guc_level, '执行失败:' + text)
        check_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(check_level_msg)
        self.common.equal_sql_mdg(check_level_msg, self.config_level,
                                  'OFF,OFF', '(1 row)', flag='1')

        text = "---step2:设置为小写off,查看参数,expect:设置成功---"
        self.logger.info(text)
        guc_level = self.sh_primy.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.config_level}='off,off'")
        self.assertTrue(guc_level, '执行失败:' + text)
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, self.config_level,
                                  'off,off', '(1 row)', flag='1')

        text = "---step3:log_min_duration_statement设置为0，执行sql语句，" \
               "查询statement_history表记录,expect:设置成功,执行sql语句成功," \
               "statement_history表数据无记录---"
        self.logger.info(text)
        sql_cmd = f"set log_min_duration_statement=0;" \
                  f"drop table if exists {self.table_name};" \
                  f"create table {self.table_name}( a int,b int);" \
                  f"select pg_sleep(60)"
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      sql_msg, '执行失败:' + text)
        select_cmd = f"select * from statement_history " \
                     f"where query like '%{self.table_name}%';"
        select_msg = self.sh_primy.execut_db_sql(select_cmd, dbname='postgres')
        self.logger.info(select_msg)
        self.assertIn('(0 rows)', select_msg, '执行失败:' + text)

        text = '---step4:设置为小写l，查看参数,expect:设置成功---'
        self.logger.info(text)
        guc_level = self.sh_primy.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.config_level}='l0,l0'")
        self.assertTrue(guc_level, '执行失败:' + text)
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, self.config_level,
                                  'l0,l0', '(1 row)', flag='1')

        text = '---step5:设置为默认值，查看参数,expect:设置成功---'
        self.logger.info(text)
        guc_level = self.sh_primy.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.config_level}")
        self.assertTrue(guc_level, '执行失败:' + text)
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, self.config_level,
                                  self.level_default, '(1 row)', flag='1')

        text = '---step6:设置为取值范围外的值，查看参数,' \
               'expect:guc执行成功，查询参数仍为上次修改后的值---'
        self.logger.info(text)
        guc_level = self.sh_primy.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.config_level}='L4,L2'")
        self.assertTrue(guc_level, '执行失败:' + text)
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, self.config_level,
                                  self.level_default, '(1 row)', flag='1')

        text = '---step7:设置为不带L的值，查看参数,' \
               'expect:guc执行成功，查询参数仍为上次修改后的值---'
        self.logger.info(text)
        guc_level = self.sh_primy.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.config_level}='2,2'")
        self.assertTrue(guc_level, '执行失败:' + text)
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, self.config_level,
                                  self.level_default, '(1 row)', flag='1')

        text = '---step8:设置分隔符为逗号以外的符号，查看参数,' \
               'expect:guc执行成功，查询参数仍为上次修改后的值---'
        self.logger.info(text)
        guc_level = self.sh_primy.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.config_level}='L1.L1'")
        self.assertTrue(guc_level, '执行失败:' + text)
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, self.config_level,
                                  self.level_default, '(1 row)', flag='1')

        text = '---step9:设置为Level格式的值，查看参数,' \
               'expect:guc执行成功，查询参数仍为上次修改后的值---'
        self.logger.info(text)
        guc_level = self.sh_primy.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                f"{self.config_level}='Level2,Level1'")
        self.assertTrue(guc_level, '执行失败:' + text)
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, self.config_level,
                                  self.level_default, '(1 row)', flag='1')

    def tearDown(self):
        text = '---step6:环境清理，expect:成功---'
        self.logger.info(text)
        drop_cmd = f"drop table {self.table_name};"
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        text = '---检查参数，恢复原值:track_stmt_stat_level---'
        self.logger.info(text)
        check_level = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(check_level)
        current = check_level.splitlines()[-2].strip()
        if self.level_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_level}={self.level_default}')
            self.assertTrue(result, '参数恢复失败')
        show_enable_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_enable_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text)
        self.common.equal_sql_mdg(show_enable_msg, self.config_level,
                                  self.level_default, '(1 row)', flag='1')
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0026 finish---')
